<?php
// @formatter:off
/**
 * @file WCDBTable.php
 * @author Alejandro Dario Simi
 * @date $Date: 2013-06-04 03:02:53 +0000 (Tue, 04 Jun 2013) $
 *
 * $Id: WCDBTable.php 68 2013-06-04 03:02:53Z daemonraco@gmail.com $
 * $URL: http://wcomix.googlecode.com/svn/tags/wcomix-1.0-BETA1/includes/db/WCDBTable.php $
 */
// @formatter:on

require_once "{$wcPaths["includes-directory"]}/db/WCDBItem.abs.php";

/**
 * @class WCDBTable
 * This class represents a database table, it hold the logic needed to check its
 * structure against and XML specification file and to give a way to recover it.
 *
 * @warning: this is valid only for MySQL databases.
 */
class WCDBTable extends WCDBItem {
	/**
	 * @var string This variable will contain the current comment of the
	 * table. By default it's empty but it may contain a value when the
	 * database table is loaded.
	 */
	protected $_commentInTable = "";
	//
	// Public methods.
	/**
	 * This method is the one in charge to perform any needed action to verify
	 * if the represented database table is as it's specified by the XML file.
	 *
	 * @param multitype $info This object will contain entries for each issue
	 * found while checking. It's contents and structure would be something
	 * like this:
	 * @code
	 *	array(
	 *	    "errors"   => array(),
	 *	    "warnings" => array(),
	 *	    "not.in"   => array(
	 *	        "table"  => array(),
	 *	        "xml"    => array()
	 *	    ),
	 *	    "differ"   => array()
	 *	)
	 * @endcode
	 * @return boolean Returns true when it is as it has to be.
	 */
	public function check(&$info = null) {
		//
		// Setting a default value to be returned.
		$ok = true;
		//
		// Creating a list for all the results and its basic structure. At
		// the end, it will hold the value for $info.
		// @{
		$results = array();
		$results["errors"] = array();
		$results["warnings"] = array();
		$results["not.in"] = array(
			"table" => array(),
			"xml" => array()
		);
		$results["differ"] = array();
		// @}
		//
		// Checking that the specification is successfully loaded.
		if($this->loaded()) {
			//
			// Creating a statement to get all the required index
			// information.
			// @{
			$stmt = null;
			$stmtId = __CLASS__."::".__FUNCTION__;
			if($this->_db->has($stmtId)) {
				$stmt = $this->_db->get($stmtId);
			} else {
				global $wcDatabaseAccess;

				$query = "select  c.table_catalog,\n";
				$query .= "        c.table_schema,\n";
				$query .= "        c.table_name,\n";
				$query .= "        c.column_name,\n";
				$query .= "        c.ordinal_position,\n";
				$query .= "        c.column_default,\n";
				$query .= "        c.is_nullable,\n";
				$query .= "        c.data_type,\n";
				$query .= "        c.character_maximum_length,\n";
				$query .= "        c.character_octet_length,\n";
				$query .= "        c.numeric_precision,\n";
				$query .= "        c.numeric_scale,\n";
				$query .= "        c.character_set_name,\n";
				$query .= "        c.collation_name,\n";
				$query .= "        c.column_type,\n";
				$query .= "        c.column_key,\n";
				$query .= "        c.extra,\n";
				$query .= "        c.privileges,\n";
				$query .= "        c.column_comment,\n";
				$query .= "        t.table_comment\n";
				$query .= "from    information_schema.columns c\n";
				$query .= "                inner join information_schema.tables t\n";
				$query .= "                        on(t.table_name = c.table_name and t.table_schema = c.table_schema)\n";
				$query .= "where   c.table_schema      = '{$wcDatabaseAccess["database"]}'\n";
				$query .= " and    lower(c.table_name) = lower(:table)\n";
				$query .= "order by c.ordinal_position asc\n";

				$stmt = $this->_db->prepare($stmtId, $query);
			}
			// @}
			//
			// Creating a list to hold table's columns specs.
			$table = array();
			//
			// Clearing the current table comment variable.
			$this->_commentInTable = "";
			//
			// Asking for the information.
			// @{
			if($stmt->execute(array(":table" => $this->name()))) {
				foreach($stmt->fetchAll() as $row) {
					//
					// Loading all table's columns taking care
					// of it position.
					$table[$row["ordinal_position"]] = $row;
					//
					// Based on the used query, every row has
					// the comment for the table.
					$this->_commentInTable = $row["table_comment"];
				}
			}
			// @}
			//
			// Seeking over every specified column to check if it's
			// present in the database table and if its structure is
			// ok.
			foreach($this->_xml->columns->children() as $xColumn) {
				//
				// This flag indicates if there is a match between
				// XML and database table specifications.
				$found = false;
				//
				// Seeking over every database table column to
				// check if its structure is correct.
				foreach($table as $column) {
					//
					// Checking if the column's name matches.
					if($xColumn->name == $column["column_name"]) {
						//
						// Checking if the column's
						// structure matches.
						if(!$this->checkTypes($xColumn, $column, $results)) {
							//
							// It would seem that something doesn't match.
							$results["differ"][] = $column["column_name"];
							$ok = false;
						}
						//
						// At this point column is
						// supposed to be found.
						$found = true;
						//
						// Since a match was found, this
						// loop must stop.
						break;
					}
				}
				//
				// When all table columns from database where
				// checked and no one matched, this name must be
				// added to the proper list.
				if(!$found) {
					$results["not.in"]["table"][] = "{$xColumn->name}";
					$ok = false;
				}
			}
			//
			// Searching for any table column specified in the
			// database that doesn't match with XML specifications.
			foreach($table as $column) {
				//
				// This flag indicates if there is a match between
				// XML and database table specifications.
				$found = false;
				//
				// Seeking over every table column specified by
				// XML file.
				foreach($this->_xml->columns->children() as $xColumn) {
					//
					// If names match, it is noted and this
					// loop ends.
					if($xColumn->name == $column["column_name"]) {
						$found = true;
						break;
					}
				}
				//
				// Checking that a match was found, otherwise, it
				// is an error and it must be informed in the
				// proper list.
				if(!$found) {
					$results["not.in"]["xml"][] = $column["column_name"];
					$ok = false;
				}
			}
			//
			// Checking if table comment is the one it should be.
			if($this->_commentInTable != $this->comment()) {
				$results["error"][] = "Table '{$this->name()}' comment differs. x: '{$this->comment()}'. t: '{$this->_commentInTable}'";
				$ok = false;
			}
		}
		//
		// If it was specified an information pointer, it is replaced with
		// collected results.
		if($info !== null) {
			$info = $results;
		}
		//
		// Returning check status.
		return $ok;
	}
	/**
	 * This method searches into all database tables of current schema looking
	 * for one with name specified by the XML file.
	 *
	 * @return boolean Returns true when the table is created.
	 */
	public function checkExistence() {
		//
		// Setting a default value to be returned.
		$ok = false;
		//
		// Checking if a XML file was successfully loaded.
		if($this->loaded()) {
			//
			// Creating an statement to ask for the table.
			// @{
			$stmt = null;
			$stmtId = __CLASS__."::".__FUNCTION__;
			if($this->_db->has($stmtId)) {
				$stmt = $this->_db->get($stmtId);
			} else {
				global $wcDatabaseAccess;

				$query = "select  *\n";
				$query .= "from    information_schema.tables\n";
				$query .= "where   table_schema      = '{$wcDatabaseAccess["database"]}'\n";
				$query .= " and    lower(table_name) = lower(:table)\n";

				$stmt = $this->_db->prepare($stmtId, $query);
			}
			// @}
			//
			// Asking for the index.
			// @{
			if($stmt->execute(array(":table" => $this->name())) && $stmt->rowCount() > 0) {
				//
				// When the statement returns rows, it means it
				// exists.
				$ok = true;
			}
			// @}
			//
			// Setting the internal flag for further existence checks.
			$this->_exists = $ok;
		}
		//
		// Returning check results.
		return $ok;
	}
	/**
	 * This method returns a list of queries that can be used to alter the
	 * database and leave it as it is specified on XML files.
	 *
	 * @return string[] Returns a list of queries to adapt the database. When
	 * it's not yet loaded, it returns false.
	 */
	public function ddlQueries() {
		//
		// Setting a default value to be returned.
		$queries = false;
		//
		// Checking the object is loaded.
		if($this->loaded()) {
			//
			// Creating a list to hold queries.
			$queries = array();
			//
			// If the index doesn't exist, it must be created.
			if(!$this->exists()) {
				$queries[] = $this->createQuery();
			} else {
				//
				// At this point, the table may be created, but
				// something may not be as it should.
				//
				// Loading global pointers.
				global $wcDatabase;
				//
				// Checking table status.
				// @{
				$info = array();
				$this->check($info);
				// @}
				//
				// If the aggressive mode is active and a not
				// specified column is found in the table, it must
				// be removed.
				if($wcDatabase["aggressive"]) {
					foreach($info["not.in"]["xml"] as $column) {
						//
						// Adding a statement for each
						// unwanted column.
						$queries[] = $this->dropColumnQuery($column);
					}
				}
				//
				// Creating statements for all those specified
				// columns not present in the database table.
				foreach($info["not.in"]["table"] as $column) {
					$queries[] = $this->createColumnQuery($column);
				}
				//
				// Creating modification statements for all
				// columns that are specified, but their structure
				// is different.
				foreach($info["differ"] as $column) {
					$queries[] = $this->modifyColumnQuery($column);
				}
				//
				// Creating a simple statement to add a comment on
				// the table.
				if($this->_commentInTable != $this->comment()) {
					$queries[] = "alter table {$this->name()} comment '{$this->comment()}'";
				}
			}
		}
		//
		// Returning the list of queries.
		return $queries;
	}
	/**
	 * This method allows to load this object with a XML file.
	 *
	 * @param string $filename Full path relative to wcomix root directory.
	 * @return boolean Returns true when the XML file was loaded successfully.
	 */
	public function load($filename) {
		//
		// Calling parent class loader.
		if(parent::load($filename)) {
			//
			// Building object name.
			$this->_name = "{$this->_dbprefix}{$this->_xml->name}";
		}
		//
		// Returning loading status.
		return $this->_loaded;
	}
	//
	// Protected methods.
	/**
	 * This method checks for any inconsistency on the column structure.
	 *
	 * @param SimpleXMLElement $xColumn XML table column specs.
	 * @param string[string] $tColumn Database table column specs.
	 * @param multitype $info Information structure. See method 'check()'.
	 * @return boolean Returns true when the structure matches.
	 */
	protected function checkTypes(&$xColumn, &$tColumn, &$info) {
		//
		// Setting the default value to be returned.
		$ok = true;
		//
		// Checking if the column's type is as it's specified.
		if($xColumn->column_type != $tColumn["column_type"]) {
			$info["error"][] = "'{$xColumn->name}' type differs. x: '{$xColumn->column_type}'. t: '{$tColumn["column_type"]}'";
			$ok = false;
		}
		//
		// Checking if the column not null constraint is as it's
		// specified.
		if("{$xColumn->null}" != $tColumn["is_nullable"]) {
			$info["error"][] = "'{$xColumn->name}' nullable constraint differs. x: '{$xColumn->null}'. t: '{$tColumn["is_nullable"]}'";
			$ok = false;
		}
		//
		// Checking if the column default value is the specified one.
		if("{$xColumn->default}" != $tColumn["column_default"]) {
			$info["error"][] = "'{$xColumn->name}' default value differs. x: '{$xColumn->default}'. t: '{$tColumn["column_default"]}'";
			$ok = false;
		}
		//
		// Checking if the column's comment is the specified one.
		if($this->comment($xColumn->comment) != $tColumn["column_comment"]) {
			$info["error"][] = "'{$xColumn->name}' comment differs. x: '{$this->comment($xColumn->comment)}'. t: '{$tColumn["column_comment"]}'";
			$ok = false;
		}
		//
		// Checking if the column's position is as it's specified.
		// This is not actually a great problem, therefore this method
		// won't return false only for this reason.
		if($xColumn->position != $tColumn["ordinal_position"]) {
			$info["warning"][] = "'{$xColumn->name}' position differs. x: '{$xColumn->position}'. t: '{$tColumn["ordinal_position"]}'";
		}
		//
		// Returning status.
		return $ok;
	}
	/**
	 * This method builds a query part that may be used to alter/set a
	 * column's structure.
	 *
	 * @param string $column Name of column.
	 * @return string Proper query part for the specified column.
	 */
	protected function columnQuery($column) {
		//
		// Setting the default value to be returned.
		$query = "";
		//
		// This is the list of keyword that can't be surrounded by single
		// quotes.
		static $defaultExceptions = array(
			"CURRENT_TIMESTAMP",
			"NULL"
		);
		//
		// Searching for the right column.
		foreach($this->_xml->columns->children() as $xColumn) {
			//
			// Building when found.
			if("{$xColumn->name}" == $column) {
				//
				// Adding name and type.
				$query .= "{$xColumn->name} {$xColumn->column_type}";
				//
				// Adding the not null constraint if its required.
				$query .= ($xColumn->null == "NO" ? " not null" : "");
				//
				// If there's a default value specified, it is
				// added.
				if(isset($xColumn->default)) {
					//
					// Checking if the default value may be
					// surrounded by single quotes.
					if(in_array(strtoupper("{$xColumn->default}"), $defaultExceptions)) {
						$query .= " default {$xColumn->default}";
					} else {
						$query .= " default '{$xColumn->default}'";
					}
				}
				//
				// Adding the auto increment flag.
				// @note By specification, when a column is
				// auto_incremet, MySQL requires it to be a
				// primary key.
				if(isset($xColumn->autoincrement)) {
					$query .= " primary key auto_increment";
				}
				//
				// Adding column comment.
				if("{$xColumn->comment}" !== "") {
					$query .= " comment '{$this->comment($xColumn->comment)}'";
				}
				//
				// Since the column was found, this loop must end.
				break;
			}
		}
		//
		// Returning the built query part.
		return $query;
	}
	/**
	 * This method allows to clean a comment.
	 *
	 * @param string $comment Comment string to be cleaned.
	 * @return string Cleaned comment.
	 */
	protected function comment(&$comment = false) {
		//
		// Setting the default value to be returned.
		$out = "";
		//
		// Checking if there is a specific comment to clean or if it has
		// to use table comment..
		if($comment !== false) {
			$out = str_replace("'", "-", "{$comment}");
		} else {
			$out = str_replace("'", "-", "{$this->_xml->comment}");
		}
		//
		// Returning comment.
		return $out;
	}
	/**
	 * This method generates a query to create a table from the scratch,
	 * unless the table does exists but with an old name.
	 *
	 * @return string Returns the proper query for table creation.
	 */
	protected function createQuery() {
		//
		// Setting the default value to be returned.
		$query = "";
		//
		// This variable will contain the current name of the table when
		// it should be renamed.
		$rename = "";
		//
		// If there are old names specified for the table, it may be
		// possible to rename it instead of creating it.
		if(isset($this->_xml->old_names)) {
			//
			// Creating a statement to ask for old table names in the
			// schema.
			// @{
			$stmt = null;
			$stmtId = __CLASS__."::".__FUNCTION__;
			if($this->_db->has($stmtId)) {
				$stmt = $this->_db->get($stmtId);
			} else {
				global $wcDatabaseAccess;

				$query = "select  *\n";
				$query .= "from    information_schema.tables\n";
				$query .= "where   table_schema      = '{$wcDatabaseAccess["database"]}'\n";
				$query .= " and    lower(table_name) = lower(:table)\n";

				$stmt = $this->_db->prepare($stmtId, $query);
			}
			// @}
			//
			// Looking for tables with the old name.
			foreach($this->_xml->old_names->children() as $name) {
				$olderName = "{$this->_dbprefix}{$name}";
				if($stmt->execute(array(":table" => $olderName)) && $stmt->rowCount() > 0) {
					//
					// At this point, the table was found with
					// and old name.
					$rename = $olderName;
					break;
				}
			}
		}
		//
		// Checking if the table must be renamed or created from the
		// scratch.
		if($rename != "") {
			//
			// Build the statement to rename the old table.
			$query = "rename table {$rename}\n";
			$query .= "        to {$this->name()}";
		} else {
			//
			// Creating a list for column's specifications.
			$columns = array();
			//
			// Building subparts of the query for each column.
			foreach($this->_xml->columns->children() as $xColumn) {
				$columns[] = "\t".$this->columnQuery("{$xColumn->name}");
			}
			//
			// Building the final query.
			$query = "create table {$this->name()} (\n";
			$query .= implode(",\n", $columns)."\n";
			$query .= ")\n";
			//
			// If there's a query specified for the table, it is added
			// to the query.
			if($this->comment() != "") {
				$query .= "comment '{$this->comment()}'\n";
			}
		}
		//
		// Returning the resulting query.
		return $query;
	}
	/**
	 * This method generates a query string to create a single column.
	 *
	 * @param string $column Column name.
	 * @return string Column creation query.
	 */
	protected function createColumnQuery($column) {
		//
		// Setting the default value to be return.
		$query = false;
		//
		// Asking for the piece of query with the column structure.
		$format = $this->columnQuery($column);
		//
		// Building the final query to create the column structure.
		if($format) {
			$query = "alter table {$this->name()}\n";
			$query .= "         add column {$format}";
		}
		//
		// Returning built query.
		return $query;
	}
	/**
	 * This method generates a query string to drop a single column.
	 *
	 * @param string $column Column name.
	 * @return string Column drop query.
	 */
	protected function dropColumnQuery($column) {
		$query = "alter table {$this->name()}\n";
		$query .= "        drop column {$column}\n";

		return $query;
	}
	/**
	 * This method generates a query string to update a single column's
	 * structure.
	 *
	 * @param string $column Column name.
	 * @return string Column modification query.
	 */
	protected function modifyColumnQuery($column) {
		//
		// Setting the default value to be return.
		$query = false;
		//
		// Asking for the piece of query with the column structure.
		$format = $this->columnQuery($column);
		//
		// Building the final query to update the column structure.
		if($format) {
			$query = "alter table {$this->name()}\n";
			$query .= "         modify column {$format}";
		}
		//
		// Returning built query.
		return $query;
	}
}
?>
